using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace Acms.SqlDal
{
    public class UserDal : Base
    {
        public UserDal() : base() { }

        public enum Fields
        {
            Id,
            SiteId,
            UserName,
            FirstName,
            LastName,
            DateOfBirth,
            EmailAddress,
            EmailAuthenticated,
            Gender,
            Address1,
            Address2,
            Town,
            PostCode,
            County,
            MobileTel,
            CustomBool1,
            CustomBool2,
            CustomBool3,
            CustomInt1,
            CustomInt2,
            CustomInt3,
            CustomString1,
            CustomString2,
            CustomString3,
            Created,
            Password,
            Salt,
            Locked,
            LastLogin,
            LastActivity,
            LastChangedPassword,
            LastLocked
        }

        protected override void ReadRecord(Hashtable table, SqlDataReader reader)
        {
            GetReaderValue<int>(Fields.Id, reader, table);
            GetReaderValue<int>(Fields.SiteId, reader, table);
            GetReaderValue<string>(Fields.UserName, reader, table);
            GetReaderValue<string>(Fields.FirstName, reader, table);
            GetReaderValue<string>(Fields.LastName, reader, table);
            GetReaderValue<DateTime?>(Fields.DateOfBirth, reader, table);
            GetReaderValue<string>(Fields.EmailAddress, reader, table);
            GetReaderValue<bool>(Fields.EmailAuthenticated, reader, table);
            GetReaderValue<byte>(Fields.Gender, reader, table);
            GetReaderValue<string>(Fields.Address1, reader, table);
            GetReaderValue<string>(Fields.Address2, reader, table);
            GetReaderValue<string>(Fields.Town, reader, table);
            GetReaderValue<string>(Fields.PostCode, reader, table);
            GetReaderValue<string>(Fields.County, reader, table);
            GetReaderValue<string>(Fields.MobileTel, reader, table);
            GetReaderValue<bool?>(Fields.CustomBool1, reader, table);
            GetReaderValue<bool?>(Fields.CustomBool2, reader, table);
            GetReaderValue<bool?>(Fields.CustomBool3, reader, table);
            GetReaderValue<int?>(Fields.CustomInt1, reader, table);
            GetReaderValue<int?>(Fields.CustomInt2, reader, table);
            GetReaderValue<int?>(Fields.CustomInt3, reader, table);
            GetReaderValue<string>(Fields.CustomString1, reader, table);
            GetReaderValue<string>(Fields.CustomString2, reader, table);
            GetReaderValue<string>(Fields.CustomString3, reader, table);
            GetReaderValue<DateTime>(Fields.Created, reader, table);
            GetReaderValue<string>(Fields.Password, reader, table);
            GetReaderValue<int>(Fields.Salt, reader, table);
            GetReaderValue<bool>(Fields.Locked, reader, table);
            GetReaderValue<DateTime?>(Fields.LastLogin, reader, table);
            GetReaderValue<DateTime?>(Fields.LastActivity, reader, table);
            GetReaderValue<DateTime?>(Fields.LastChangedPassword, reader, table);
            GetReaderValue<DateTime?>(Fields.LastLocked, reader, table);
        }

        public System.Collections.Hashtable[] GetAll()
        {
            SqlCommand cmd = CreateCommand("GetAll");
            return ExecuteGetCollection(cmd);
        }

        public Hashtable GetById(int id)
        {
            SqlCommand cmd = CreateCommand("GetById");
            AddInputParameter(cmd, "Id", id, SqlDbType.Int);
            return ExecuteGetItem(cmd);
        }

        public Hashtable GetByUserName(string userName, int siteId)
        {
            SqlCommand cmd = CreateCommand("GetByUserName");
            AddInputParameter(cmd, "UserName", userName, SqlDbType.VarChar, 50);
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return ExecuteGetItem(cmd);
        }

        public Hashtable GetByEmailAddress(string emailAddress, int siteId)
        {
            SqlCommand cmd = CreateCommand("GetByEmailAddress");
            AddInputParameter(cmd, "EmailAddress", emailAddress, SqlDbType.VarChar, 250);
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return ExecuteGetItem(cmd);
        }

        public Hashtable[] GetAllByCustomProperty(int siteId, string name, string value)
        {
            SqlCommand cmd = CreateCommand("GetAllByCustomProperty");
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            AddInputParameter(cmd, "Name", name, SqlDbType.VarChar, 20);
            AddInputParameter(cmd, "Value", value, SqlDbType.VarChar, 2000);
            return ExecuteGetCollection(cmd);
        }

        public Hashtable[] GetAllByRoleIdAndSiteId(int roleId, int siteId)
        {
            SqlCommand cmd = CreateCommand("GetAllByRoleIdAndSiteId");
            AddInputParameter(cmd, "RoleId", roleId, SqlDbType.Int);
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return ExecuteGetCollection(cmd);
        }

        public Hashtable GetFromLoginKey(Guid key, DateTime lastAccessed)
        {
            SqlCommand cmd = CreateCommand("GetFromLoginKey");
            AddInputParameter(cmd, "Key", key, SqlDbType.UniqueIdentifier);
            AddInputParameter(cmd, "LastAccessed", lastAccessed, SqlDbType.DateTime);
            return ExecuteGetItem(cmd);
        }

        public void DeleteLoginKey(Guid key, int id)
        {
            SqlCommand cmd = CreateCommand("DeleteLoginKey");
            AddInputParameter(cmd, "Key", key, SqlDbType.UniqueIdentifier);
            AddInputParameter(cmd, "UserId", id, SqlDbType.Int);
            ExecuteCommand(cmd);
        }

        public Hashtable[] Search(int siteId)
        {
            SqlCommand cmd = CreateCommand("Search");
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return ExecuteGetCollection(cmd);
        }


        public Hashtable[] GetGloabal()
        {
            SqlCommand cmd = CreateCommand("GetGlobal");
            return ExecuteGetCollection(cmd);
        }

        public void Update(ref int id, int siteId, string userName, string firstName, string lastName, DateTime? dateOfBirth, string emailAddress, bool emailAuthenticated, byte gender, string address1, string address2, string town, string postCode, string county, string mobileTel, DateTime created, string password, int salt, bool? customBool1, bool? customBool2, bool? customBool3, int? customInt1, int? customInt2, int? customInt3, string customString1, string customString2, string customString3, bool? locked, DateTime? lastLogin, DateTime? lastActivity, DateTime? lastChangedPassword, DateTime? lastLocked, ref object concurencyId)
        {
            SqlCommand cmd = CreateCommand("Put");
            AddInputParameter(cmd, "Id", id, SqlDbType.Int);
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            AddInputParameter(cmd, "UserName", userName, SqlDbType.VarChar, 50);
            AddInputParameter(cmd, "FirstName", firstName, SqlDbType.VarChar, 50);
            AddInputParameter(cmd, "LastName", lastName, SqlDbType.VarChar, 50);
            AddInputParameter(cmd, "DateOfBirth", dateOfBirth, SqlDbType.DateTime);
            AddInputParameter(cmd, "EmailAddress", emailAddress, SqlDbType.VarChar, 100);
            AddInputParameter(cmd, "EmailAuthenticated", emailAuthenticated, SqlDbType.Bit);
            AddInputParameter(cmd, "Gender", gender, SqlDbType.TinyInt);
            AddInputParameter(cmd, "Address1", address1, SqlDbType.VarChar, 100);
            AddInputParameter(cmd, "Address2", address2, SqlDbType.VarChar, 100);
            AddInputParameter(cmd, "Town", town, SqlDbType.VarChar, 50);
            AddInputParameter(cmd, "PostCode", postCode, SqlDbType.VarChar, 20);
            AddInputParameter(cmd, "County", county, SqlDbType.VarChar, 20);
            AddInputParameter(cmd, "MobileTel", mobileTel, SqlDbType.VarChar, 20);
            AddInputParameter(cmd, "Created", created, SqlDbType.DateTime);
            AddInputParameter(cmd, "Password", password, SqlDbType.VarChar);
            AddInputParameter(cmd, "Salt", salt, SqlDbType.Int);
            AddInputParameter(cmd, "CustomBool1", customBool1, SqlDbType.Bit);
            AddInputParameter(cmd, "CustomBool2", customBool2, SqlDbType.Bit);
            AddInputParameter(cmd, "CustomBool3", customBool3, SqlDbType.Bit);
            AddInputParameter(cmd, "CustomInt1", customInt1, SqlDbType.Int);
            AddInputParameter(cmd, "CustomInt2", customInt2, SqlDbType.Int);
            AddInputParameter(cmd, "CustomInt3", customInt3, SqlDbType.Int);
            AddInputParameter(cmd, "CustomString1", customString1, SqlDbType.VarChar, 255);
            AddInputParameter(cmd, "CustomString2", customString2, SqlDbType.VarChar, 255);
            AddInputParameter(cmd, "CustomString3", customString3, SqlDbType.VarChar, 255);
            AddInputParameter(cmd, "Locked", locked, SqlDbType.Bit);
            AddInputParameter(cmd, "LastLogin", lastLogin, SqlDbType.DateTime);
            AddInputParameter(cmd, "LastActivity", lastActivity, SqlDbType.DateTime);
            AddInputParameter(cmd, "LastChangedPassword", lastChangedPassword, SqlDbType.DateTime);
            AddInputParameter(cmd, "LastLocked", lastLocked, SqlDbType.DateTime);
            AddInputParameter(cmd, "ConcurencyId", concurencyId, SqlDbType.Timestamp);
            
            AddOutputParameter(cmd, "IdOut", SqlDbType.Int);
            AddOutputParameter(cmd, "ConcurencyIdOut", SqlDbType.Timestamp);

            ExecuteCommand(cmd);

            id = GetOutputValue<int>(cmd, "IdOut");
            concurencyId = GetOutputValue<byte[]>(cmd, "ConcurencyIdOut");
        }

        public void CreateLoginKey(Guid key, int userId, DateTime loggedIn, bool perminant)
        {
            SqlCommand cmd = CreateCommand("CreateLoginKey");
            AddInputParameter(cmd, "Key", key, SqlDbType.UniqueIdentifier);
            AddInputParameter(cmd, "UserId", userId, SqlDbType.Int);
            AddInputParameter(cmd, "LoggedIn", loggedIn, SqlDbType.DateTime);
            AddInputParameter(cmd, "Perminant", perminant, SqlDbType.Bit);
            base.ExecuteCommand(cmd);
        }

        public void Delete(int id, object concurencyId)
        {
            SqlCommand cmd = CreateCommand("Delete");
            AddInputParameter(cmd, "Id", id, SqlDbType.Int);
            AddInputParameter(cmd, "VersionId", concurencyId, SqlDbType.Timestamp);
            base.ExecuteCommand(cmd);
        }

        public Hashtable GetByEmailActivationCodeAndSiteId(string code, int siteId)
        {
            SqlCommand cmd = CreateCommand("GetByEmailActivationCodeAndSiteId");
            AddInputParameter(cmd, "Code", code, SqlDbType.VarChar ,10);
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return base.ExecuteGetItem(cmd);
        }

        public Hashtable[] GetMostRecent(int siteId, int number)
        {
            SqlCommand cmd = CreateCommand("GetMostRecent");
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            AddInputParameter(cmd, "Number", number, SqlDbType.Int);
            return base.ExecuteGetCollection(cmd);
        }

        public int GetCount(int siteId)
        {
            SqlCommand cmd = CreateCommand("GetCount");
            AddInputParameter(cmd, "SiteId", siteId, SqlDbType.Int);
            return base.ExecuteScalar<int>(cmd);
        }
    }
}
